<?php

/**
 * Created by PhpStorm.
 * User: dev
 * Date: 11/11/14
 * Time: 4:36 PM
 */
class Admin_Utility_Import_Controller extends Admin_Controller
{

    public $restful = true;

    public $duplicate = 0;
    public $imported = 0;

    public function __construct()
    {
        parent::__construct();
        //--Your Code --//
        $this->theme->set_theme("admin");
        $this->theme->set_layout("default");
    }

    public function get_index()
    {
        $this->theme->title("Utility : Import");
        $data['title'] = 'Import Utility';
        return $this->theme->render('admin::utility.import', $data);
    }

    public function post_index()
    {

    	

        $fileName = Input::file('import_file.name');
     //  
        $extension = File::extension($fileName);

      //  die($extension);
        if (!empty($fileName) && $extension != 'xls') {
            return Redirect::back()->with('message', 'Invalid File!');
        }

        $objPHPExcel = new PHPExcel();

       
        
        if ($extension == 'xls' || $extension == 'xlsx' || $extension == 'ods') {

        	
            try {
                $inputFile = $_FILES['import_file']['tmp_name'];
                //dd($inputFile);
                $inputFileType = PHPExcel_IOFactory::identify($inputFile);
                $objReader = PHPExcel_IOFactory::createReader($inputFileType);
                $objPHPExcel = $objReader->load($inputFile);
            } catch (Exception $e) {
                die($e->getMessage());
            }

             //   die('hh');
        }


        foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
            $worksheetTitle = $worksheet->getTitle();
            $highestRow = $worksheet->getHighestRow(); // e.g. 10
            $highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
            $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
            $nrColumns = ord($highestColumn) - 64;


            /*echo "<br>The worksheet " . $worksheetTitle . " has ";
            echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
            echo ' and ' . $highestRow . ' row.';
            echo '<br>Data: <table border="1"><br>';*/

            $this->imported = 0;
            $this->duplicate = 0;

            for ($row = 2; $row <= $highestRow; ++$row) {
            //	die($highestRow);

                $excelData = array(
                    'category' => Str::title($worksheet->getCellByColumnAndRow(0, $row)->getValue()),
                    'sub-category' => Str::title($worksheet->getCellByColumnAndRow(1, $row)->getValue()),
                    'sub-sub-category' => Str::title($worksheet->getCellByColumnAndRow(2, $row)->getValue()),
                    'sub-sub-sub-category' => Str::title($worksheet->getCellByColumnAndRow(3, $row)->getValue()),

                    'company' => Str::title($worksheet->getCellByColumnAndRow(4, $row)->getValue()),
                    'contact_person' => Str::title($worksheet->getCellByColumnAndRow(5, $row)->getValue()),
                    'contact_person_telephone' => $worksheet->getCellByColumnAndRow(6, $row)->getValue(),
                    'address1' => Str::title($worksheet->getCellByColumnAndRow(7, $row)->getValue()),
                    'address2' => Str::title($worksheet->getCellByColumnAndRow(8, $row)->getValue()),

                    'city' => Str::title($worksheet->getCellByColumnAndRow(9, $row)->getValue()),
                    'region' => Str::title($worksheet->getCellByColumnAndRow(10, $row)->getValue()),
                    'country' => Str::title($worksheet->getCellByColumnAndRow(11, $row)->getValue()),

                    'zip' => $worksheet->getCellByColumnAndRow(12, $row)->getValue(),
                    'phone' => $worksheet->getCellByColumnAndRow(13, $row)->getValue(),
                    'fax' => $worksheet->getCellByColumnAndRow(14, $row)->getValue(),
                    'email' => $worksheet->getCellByColumnAndRow(15, $row)->getValue(),
                    'website' => $worksheet->getCellByColumnAndRow(16, $row)->getValue(),
                    'product_service' => Str::title($worksheet->getCellByColumnAndRow(17, $row)->getValue()),
                );

                if ($excelData['company'] != '' && $excelData['address1'] != '' && $excelData['city'] != '' && $excelData['region'] && $excelData['country'] && $excelData['category']) {
                   // echo $excelData['company'] . "  Data OK...in ROW : " . $row . "</br>";

                    $title = trim($excelData['company']);
                    $address1 = trim($excelData['address1']);

                    $sql = "select id from post where title = '" . trim($title) . "' and address1 = '" . trim($address1) . "' limit 1";
                    $chk = DB::query($sql);
                    if(!$chk){
                        $this->imported++;
                        $this->checkData($excelData);
                    }else{
                        $this->duplicate++;
                    }

                }


            }

            $imp = "IMPORTED : " . $this->imported;
            $rej = "REJECTED : " . $this->duplicate;
            //die();
            return Redirect::back()->with('message', $imp .' '.$rej);
        }
    }


    private function checkData($data)
    {
        DB::disableQueryLog();
        ini_set('memory_limit', '256M');

        $pass = false;


        $post = $this->check_post($data);
        if ($post) {

            $parent_cat = $this->check_category(0, $data['category']);
            $child_cat = ($data['sub-category'] != '' ? $this->check_category($parent_cat, $data['sub-category']) : null);
            $child_cat2 = ($data['sub-sub-category'] != '' ? $this->check_category($child_cat, $data['sub-sub-category']) : null);
            $child_cat3 = ($data['sub-sub-sub-category'] != '' ? $this->check_category($child_cat2, $data['sub-sub-sub-category']) : null);

            $country = $this->check_location(0, $data['country']);
            $state = $this->check_location($country, $data['region']);
            $city = $this->check_location($state, $data['city']);

            $this->insert_terms($post, 'category', $parent_cat, 'Category');
            if ($child_cat) $this->insert_terms($post, 'category', $child_cat, 'Category');
            if ($child_cat2) $this->insert_terms($post, 'category', $child_cat2, 'Category');
            if ($child_cat3) $this->insert_terms($post, 'keyword', $child_cat3, 'Keyword');

            $this->insert_terms($post, 'location', $country, 'Country');
            $this->insert_terms($post, 'location', $state, 'Region');
            $this->insert_terms($post, 'location', $city, 'City');

        }

        //--Clear Caches
        $cache_path = path('storage') . 'cache/';
        File::cleandir($cache_path);

    }

    function check_post($data)
    {
        $title = trim($data['company']);
        $address1 = trim($data['address1']);

        $sql = "select id from post where title = '" . trim($title) . "' and address1 = '" . trim($address1) . "' limit 1";
        $chk_post = DB::query($sql);


        if (!$chk_post) {

            $pData = array(
                'users_id' => Auth::user()->id,
                'title' => $title,
                'address1' => $address1,
                'address2' => $data['address2'],
                'phone1' => $data['phone'],
                'fax' => $data['fax'],
                'email' => $data['email'],
                'website' => $data['website'],

                'slug' => Str::slug($title),
                'meta_title' => $title,
                'meta_description' => $title . ', ' . $data['product_service'],
                'meta_keyword' => $title . ', ' . $data['product_service'],
                'services' => $data['product_service'],
                'contact_name' => $data['contact_person'],
                'contact_no' => $data['contact_person_telephone'],
                'created_at' => Commoncall::current_timestamp(),
            );

            if ($title != '' && $address1 != '' && $data['city'] != '' && $data['region'] && $data['country'] && $data['category'] && $data['sub-category']) {
                $insert_id = DB::table('post')->insert_get_id($pData);

                return $insert_id;
            }

        }

    }

    private function check_category($parent = 0, $str)
    {
        if ($str != '') {
            $chk_category = DB::table('categories')->where('parent_id', '=', $parent)->where('name', '=', $str)->first();
            if ($chk_category) {
                return $chk_category->id;
            } else {
                $pData = array(
                    'name' => $str,
                    'slug' => Str::slug($str),
                    'parent_id' => $parent,
                    'description' => $str,
                    'meta_title' => $str,
                    'meta_keyword' => $str,
                    'meta_description' => $str
                );

                $insertID = Hierarchy::insert('categories', $pData);
                return $insertID;
            }
        }
    }

    private function check_location($parent = 0, $str)
    {

        if ($str != '') {
            $chk_category = DB::table('locations')->where('parent_id', '=', $parent)->where('name', '=', $str)->first();
            if ($chk_category) {
                return $chk_category->id;
            } else {
                $pData = array(
                    'name' => $str,
                    'parent_id' => $parent,
                    'slug' => Str::slug($str),
                    'meta_title' => $str,
                    'meta_keyword' => $str,
                    'meta_description' => $str
                );

                $insertID = Hierarchy::insert('locations', $pData);
                return $insertID;
            }
        }
    }

    function insert_terms($postID, $term, $value, $caption)
    {
        $dd = array(
            'post_id' => $postID,
            'term' => $term,
            'value' => $value,
            'caption' => $caption,
        );
        return DB::table('terms')->insert($dd);
    }
}
